/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package mysql;

import com.mysql.jdbc.*;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.TimeZone;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 *
 * @author John
 */
public class MysqlHandler {

    private Connection conn = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;

    public void createConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        // Setup the connection with the DB
        conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/?"
                + "user=root&password=root");

        createTable();

    }

    public void closeConnection() throws SQLException {
        conn.close();
    }

    public void createTable() throws SQLException {

        preparedStatement = (PreparedStatement) conn
                .prepareStatement("CREATE DATABASE IF NOT EXISTS `securityprojectdb`");
        preparedStatement.executeUpdate();
        preparedStatement = (PreparedStatement) conn
                .prepareStatement("CREATE TABLE IF NOT EXISTS  `securityprojectdb`.`users`"
                        + "  (imei           VARCHAR(50),"
                        + "   email     VARCHAR(50),"
                        + "   password     VARCHAR(50),"
                        + "   token integer, PRIMARY KEY (`imei`))");

        preparedStatement.executeUpdate();

        preparedStatement = (PreparedStatement) conn
                .prepareStatement("CREATE TABLE IF NOT EXISTS  `securityprojectdb`.`passwords`"
                        + "  (imei           VARCHAR(50),"
                        + "   email     VARCHAR(50),"
                        + "   creation_time     integer(50),"
                        + "   expiration_time     integer(50),"
                        + "   temp_pass     VARCHAR(50), PRIMARY KEY (`imei`))");

        preparedStatement.executeUpdate();
    }

    public int chechAvailability(String IMEI, String email) throws SQLException {

        ResultSet resultSet = null;

        preparedStatement = (PreparedStatement) conn
                .prepareStatement("SELECT imei FROM `securityprojectdb`.`users` WHERE  imei='" + IMEI + "'");
        resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {

            preparedStatement = (PreparedStatement) conn
                    .prepareStatement("SELECT email FROM `securityprojectdb`.`users` WHERE  email='" + email + "'");
            resultSet = preparedStatement.executeQuery();
            if (!resultSet.next()) {
                return 1;
            } else {
                return 3;
            }

        } else {
            return 2;
        }

    }

    public boolean checkRegistered(String IMEI) throws SQLException {
        ResultSet resultSet = null;
        preparedStatement = (PreparedStatement) conn
                .prepareStatement("SELECT imei FROM `securityprojectdb`.`users` WHERE  imei='" + IMEI + "'");
        resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {
            return false;
        } else {
            return true;
        }

    }

    public void registerUser(String password, String imei, String email) {

        try {
            preparedStatement = (PreparedStatement) conn
                    .prepareStatement("INSERT INTO `securityprojectdb`.`users` (`imei`, `email`,`password`,`token`) VALUES (?, ?, ?, ?)");

            preparedStatement.setString(1, imei);
            preparedStatement.setString(2, email);
            preparedStatement.setString(3, password);
            preparedStatement.setInt(4, 1000);

            preparedStatement.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(MysqlHandler.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public String checkTempPass(String imei) {

        ResultSet resultSet = null;
        String tempPass = null;

        try {

            preparedStatement = (PreparedStatement) conn
                    .prepareStatement("SELECT temp_pass FROM `securityprojectdb`.`passwords` WHERE imei='" + imei + "'");
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                tempPass = resultSet.getString("temp_pass");

                return tempPass;
            } else {
                return null;
            }

        } catch (SQLException ex) {
            Logger.getLogger(MysqlHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;

    }
    
    
    public boolean logInTempPass(String email, String password) {

        ResultSet resultSet = null;
        String tempPass = null;

        try {

            preparedStatement = (PreparedStatement) conn
                    .prepareStatement("SELECT * FROM `securityprojectdb`.`passwords` WHERE temp_pass='" + password + "' AND email='" + email + "'");
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) 
                return true;
            else return false;
        } catch (SQLException ex) {
            Logger.getLogger(MysqlHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;

    }

    public void registerTempPass(String password, String imei, int duration) {

        ResultSet resultSet = null;
        String email = null;

        try {

            int creationTime;
            int expTime;
            
            creationTime = getCurrentTime();
            expTime = creationTime + duration;
            
            preparedStatement = (PreparedStatement) conn
                    .prepareStatement("SELECT email FROM `securityprojectdb`.`users` WHERE imei='" + imei + "'");
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            email = resultSet.getString("email");

            preparedStatement = (PreparedStatement) conn
                    .prepareStatement("INSERT INTO `securityprojectdb`.`passwords` (`imei`,`email`,`creation_time`,`expiration_time`,`temp_pass`) VALUES (?, ?, ?, ?, ?)");

            preparedStatement.setString(1, imei);
            preparedStatement.setString(2, email);
            preparedStatement.setInt(3, creationTime);
            preparedStatement.setInt(4, expTime);
            preparedStatement.setString(5, password);

            preparedStatement.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(MysqlHandler.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
    
    public void deleteTempPass(String imei) {
    
        try {
            preparedStatement = (PreparedStatement) conn
                    .prepareStatement("DELETE FROM `securityprojectdb`.`passwords` WHERE imei='" + imei + "'");
            
            
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(MysqlHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
    }
    
    

    public int getCurrentToken(String imei) {
        int token = 0;
        ResultSet resultSet = null;

        try {
            preparedStatement = (PreparedStatement) conn
                    .prepareStatement("SELECT token FROM `securityprojectdb`.`users` WHERE imei='" + imei + "'");
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            token = resultSet.getInt("token");

            preparedStatement = (PreparedStatement) conn
                    .prepareStatement("UPDATE `securityprojectdb`.`users` SET token=" + (token - 1) + " where imei='" + imei + "'");
            preparedStatement.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(MysqlHandler.class.getName()).log(Level.SEVERE, null, ex);
        }

        return token;

    }
    
    
    public int getCurrentTokenRemainingTime(String imei) {
        int expTime;
        int currentTime;
        int timeRemaining;
        ResultSet resultSet = null;

        try {
            preparedStatement = (PreparedStatement) conn
                    .prepareStatement("SELECT expiration_time FROM `securityprojectdb`.`passwords` WHERE imei='" + imei + "'");
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            expTime = resultSet.getInt("expiration_time");
            
            currentTime= getCurrentTime();
            
            timeRemaining = (currentTime - expTime)%30;
            
           
            
            return Math.abs(timeRemaining);

        } catch (SQLException ex) {
            Logger.getLogger(MysqlHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;



    }
    
    

    public String getPassword(String imei) {
        String password = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = (PreparedStatement) conn
                    .prepareStatement("SELECT password FROM `securityprojectdb`.`users` WHERE imei='" + imei + "'");
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            password = resultSet.getString("password");

        } catch (SQLException ex) {
            Logger.getLogger(MysqlHandler.class.getName()).log(Level.SEVERE, null, ex);
        }

        return password;

    }

    public int getCurrentTime() {
        
        Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC")); 
        
        int time = cal.get(Calendar.MINUTE);
        
        return time;
        
    }
    
}
